﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DB_QLSV.BusinessClassLibrary
{
    public class MonHocBUS
    {
        private static QLSVDataContext db = new QLSVDataContext();
        public static bool ThemMonHoc(MonHoc MonHoc)
        {
            try
            {
                db.MonHocs.InsertOnSubmit(MonHoc);
                db.SubmitChanges();
            }
            catch
            {
                //conflict primary key - the item is exist
                return false;
            }
            return true;
        }
        public static bool XoaMonHoc(string idMonHoc)
        {
            try
            {
                MonHoc old = db.MonHocs.Single(kh => kh.mamonhoc == idMonHoc);
                db.MonHocs.DeleteOnSubmit(old);
                db.SubmitChanges();
            }
            catch
            {
                // the item is not exist
                return false;
            }
            return true;
        }
        public static bool CapNhatMonHocHoc(string id, MonHoc monHoc)
        {
            try
            {
                MonHoc old = db.MonHocs.Single(kh => kh.mamonhoc == id);
                old.tenmonhoc = monHoc.tenmonhoc;
                db.SubmitChanges();
            }
            catch
            {
                // the item is not exist
                return false;
            }
            return true;
        }
        public static List<MonHoc> LayDanhSach()
        {
            return db.MonHocs.ToList();
        }

        public static List<MonHoc> LayDanhSachTheoMaSoLop(String masolop)
		{
			var list = from kq in db.MonHocs
						   join lop in db.Lop_MonHocs on kq.mamonhoc equals lop.masomonhoc
						   where lop.masolop == masolop
						   select kq;
            List<MonHoc> result = new List<MonHoc>();
            foreach (MonHoc item in list)
            {
                result.Add(item);
            }
            if (result.Count > 0)
                return result;
            return null;
        }       
        //get subjects depend on BranchId and year
        public static List<StudentManagement.DataGVSubjectObject> LayDanhSach(Nganh nganhObject,DB_QLSV.KhoaHoc khObject)
        {
            List<StudentManagement.DataGVSubjectObject> lst = new List<StudentManagement.DataGVSubjectObject>();
            var query =from subject in db.MonHocs
                         join subject_class in db.Lop_MonHocs on subject.mamonhoc equals subject_class.masomonhoc into kq1
                         from _kq1 in kq1
                         join _class in db.Lops on _kq1.masolop equals _class.malop into kq2
                         from _kq2 in kq2

                         where _kq2.nganh == nganhObject.masonganh && khObject.makh == _kq2.khoahoc
                         select new
                         {
                             subject.tenmonhoc,
                             
                             subject.mota,
                             _kq2.nganh,
                             nganhObject.tennganh,
                             subject.mamonhoc,
                         };
            var queryfirst =( from subject in db.MonHocs
                         join subject_class in db.Lop_MonHocs on subject.mamonhoc equals subject_class.masomonhoc into kq1
                         from _kq1 in kq1
                         join _class in db.Lops on _kq1.masolop equals _class.malop into kq2
                         from _kq2 in kq2

                         where _kq2.nganh == nganhObject.masonganh && khObject.makh == _kq2.khoahoc
                         select new
                         {
                             subject.mamonhoc,
                         }).Distinct();
           // var query = queryfirst.Distinct(new IEqualityComparer<Gallery>
            foreach (var itemfirst in queryfirst)
            {
                foreach (var item in query)
                {
                    if (itemfirst.mamonhoc == item.mamonhoc)
                    {
                        StudentManagement.DataGVSubjectObject _new = new StudentManagement.DataGVSubjectObject();
                        _new.mamonhoc = item.mamonhoc;
                        _new.tenmonhoc = item.tenmonhoc;
                        _new.tennganh = item.tennganh;
                        _new.mota = item.mota;
                        lst.Add(_new);
                        break;
                    }
                }
            }
            //foreach (var item in query)
            //{
            //    StudentManagement.DataGVSubjectObject _new = new StudentManagement.DataGVSubjectObject();
            //    _new.mamonhoc = item.mamonhoc;
            //    _new.tenmonhoc = item.tenmonhoc;
            //    _new.tennganh = item.tennganh;
            //    _new.mota = item.mota;
            //    lst.Add(_new);
            //}
            if (lst.Count() > 0)
                return lst;
            return null;
        }
        //get subjects depend on BranchId
        public static List<StudentManagement.DataGVSubjectObject> LayDanhSach(Nganh nganhObject)
        {
            List<StudentManagement.DataGVSubjectObject> lst = new List<StudentManagement.DataGVSubjectObject>();
            var query = from subject in db.MonHocs
                        from subjectClass in db.Lop_MonHocs
                        from lop in db.Lops
                        where subject.mamonhoc == subjectClass.masomonhoc
                        where subjectClass.masolop == lop.malop
                        where lop.nganh == nganhObject.masonganh
                        select new {
                            subject.mamonhoc,
                            subject.tenmonhoc,
                            nganhObject.tennganh,
                            subject.mota
                        };
            var queryfirst = (from subject in db.MonHocs
                        from subjectClass in db.Lop_MonHocs
                        from lop in db.Lops
                        where subject.mamonhoc == subjectClass.masomonhoc
                        where subjectClass.masolop == lop.malop
                        where lop.nganh == nganhObject.masonganh
                              select new
                              {
                                  subject.mamonhoc,
                              }).Distinct();
            foreach (var itemfirst in queryfirst)
            {
                foreach (var item in query)
                {
                    if (itemfirst.mamonhoc == item.mamonhoc)
                    {
                        StudentManagement.DataGVSubjectObject _new = new StudentManagement.DataGVSubjectObject();
                        _new.mamonhoc = item.mamonhoc;
                        _new.tenmonhoc = item.tenmonhoc;
                        _new.tennganh = item.tennganh;
                        _new.mota = item.mota;
                        lst.Add(_new);
                        break;
                    }
                }
            }

            if (lst.Count() > 0)
                return lst;
            return null;
        }
        //get 
    }
}
